﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using XMK.Framework.DataAccess;
using XMK.Tadong.WorkflowBiz.Models;
using XMK.Utils;
using XMK.Tadong.WorkflowBiz.WorkFlow.Models;
using XMK.Framework.Model;
using XMK.Tadong.WorkflowBiz.WorkFlow;

namespace XMK.Tadong.WorkflowBiz.WorkFlow
{
    public class WorkFlowDataAccess
    {

         /// <summary>
        /// 数据库连接
        /// </summary>
        protected DbProvider dbProvider;

        public WorkFlowDataAccess(DbProvider dbProvider)
        {
            this.dbProvider = dbProvider;
        }


        /// <summary>
        /// 添加录入日志
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        /// <param name="operateID"></param>
        /// <param name="commentMessage"></param>
        /// <param name="fromUserID"></param>
        /// <param name="fromStatusID"></param>
        /// <param name="toUserID"></param>
        /// <param name="toStatusID"></param>
        /// <param name="IsLast"></param>
        /// <returns></returns>
        public string AddInsertLog(int tableID, Guid recordID, int operateID, string commentMessage, Guid fromUserID, int fromStatusID, Guid toUserID, int toStatusID, string signUserID, int IsLast)
        {
            string strSql = string.Empty;
            string rtnStr = string.Empty;
            try
            {

                strSql = @"insert into workflow_operatelog
                                          (operatelogid,
                                           operatetime,
                                           tableid,
                                           recordid,
                                           operateid,
                                           commentmessage,
                                           fromstatusid,
                                           tostatusid,
                                           fromuserid,
                                           touserid,
                                           signuserid,
                                           signtime,
                                           islast)
                                        values
                                          (:p_operatelogid,
                                           sysdate,
                                           :p_tableid,
                                           :p_recordid,
                                           :p_operateid,
                                           :p_commentmessage,
                                           :p_fromstatusid,
                                           :p_tostatusid,
                                           :p_fromuserid,
                                           :p_touserid,
                                           :p_signuserid,
                                           sysdate,
                                           :p_islast)";

                DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql, delegate
                {
                    return DbCmdHelper.CreateParameters(dbProvider,
                        "operatelogid", "tableid", "recordid", "operateid", "commentmessage",
                        "fromstatusid", "tostatusid","fromuserid", "touserid", "signuserid", "islast").ToArray();
                });
                cmd.DbCmdInfo.CommandType = CommandType.Text;
                cmd.SetParamValues(Guid.NewGuid(), tableID, recordID, operateID, commentMessage, fromStatusID, toStatusID, fromUserID, toUserID, signUserID, IsLast);
                DbManager.ExecNonQuery(cmd);
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        /// <summary>
        /// 新增操作日志
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        /// <param name="operateID"></param>
        /// <param name="commentMessage"></param>
        /// <param name="fromUserID"></param>
        /// <param name="fromStatusID"></param>
        /// <param name="toUserID"></param>
        /// <param name="toStatusID"></param>
        /// <returns></returns>
        public string AddNewFlowLog(int tableID, Guid recordID, int operateID, string commentMessage, Guid fromUserID, int fromStatusID, Guid toUserID, int toStatusID, bool NeedUpdateStatus)
        {
            string rtnStr = string.Empty;
            string sql = string.Empty;
            try
            {

                if (NeedUpdateStatus)
                {
                    sql = string.Format("update workflow_operatelog set islast=0 where tableid='{0}' and recordid='{1}'", tableID, recordID);
                }
                sql = string.Format(@"insert into workflow_operatelog values ('{0}',sysdate,'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',null,null,'1')",
                                                                                            Guid.NewGuid(),
                                                                                            tableID,
                                                                                            recordID,
                                                                                            operateID,
                                                                                            commentMessage,
                                                                                            fromStatusID,
                                                                                            toStatusID,
                                                                                            fromUserID,
                                                                                            toUserID);
                DbCmdHelper cmd = new DbCmdHelper(dbProvider, sql);
                cmd.DbCmdInfo.CommandType = CommandType.Text;
                DbManager.ExecNonQuery(cmd);
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        /// <summary>
        /// 驱动流程到下一阶段
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        /// <returns></returns>
        public string ProcessFlowLog(Guid operatelogID, string singUserID)
        {
            string strSql = string.Empty;
            string rtnStr = string.Empty;
            try
            {
                strSql = "update WORKFLOWOPERATELOG set signuserid=:p_signuserid and signtime=sysdate() where operatelogID=:p_OperatelogID";
                
                DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql, delegate
                {
                    return DbCmdHelper.CreateParameters(dbProvider,
                        "signuserid", "OperatelogID").ToArray();
                });
                cmd.DbCmdInfo.CommandType = CommandType.Text;
                cmd.SetParamValues(Guid.NewGuid(), singUserID, operatelogID);
                DbManager.ExecNonQuery(cmd);
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        /// <summary>
        /// 驱动业务表状态
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="recordID"></param>
        /// <param name="statusID"></param>
        public void ProcessStatus(string tableName, string recordID, int statusID)
        {
            string sql = string.Format("update {0} set statusID={1} where DataID='{2}'", tableName, statusID, recordID);
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sql);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DbManager.ExecNonQuery(cmd);
        }

        /// <summary>
        /// 本人自己关闭流程
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        public void FinishFlowLog(int tableID, string recordID, Guid toUserID)
        {
            string sql = string.Format(@"declare IsLastCount number;begin select count(*) into IsLastCount from workflow_operatelog where tableid='{0}' and recordid='{1}' and touserID='{2}' and islast=1;if IsLastCount=1 then update workflow_operatelog set IsLast=0 where tableid='{0}' and recordid='{1}' and islast=1;end if;end;", tableID, recordID, toUserID);

            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sql);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DbManager.ExecNonQuery(cmd);
        }

        /// <summary>
        /// 直接系统关闭流程
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        public void FinishFlowLog(int tableID, string recordID)
        {
            string sql = string.Format(@"declare IsLastCount number;
                                                            begin
                                                                    select count(*) into IsLastCount from workflow_operatelog where tableid='{0}' and recordid='{1}'  and islast=1;
                                                                    if IsLastCount=1 then
                                                                      update workflow_operatelog set IsLast=0 where tableid='{0}' and recordid='{1}' and islast=1;
                                                                      end if;
                                                              end;", tableID, recordID);
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sql);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            DbManager.ExecNonQuery(cmd);
            
        }

        /// <summary>
        /// 签收数据
        /// </summary>
        /// <param name="signUserID"></param>
        /// <param name="operateLogID"></param>
        /// <returns></returns>
        public string SignData(Guid signUserID, int tableID, string recordID)
        {
            string rtnStr = string.Empty;
            try
            {
                
                string sql = string.Format(@"declare isLastCount number;
begin
       select count(*) into isLastCount from workflow_operatelog where touserid='{0}' and islast=1 and tableid='{1}' and recordid='{2}';
    if isLastCount>0 then
               update workflow_operatelog set signuserid='{0}',signtime=sysdate where touserid='{0}' and islast=1 and tableid='{1}' and recordid='{2}';
               update workflow_operatelog set islast=0 where tableid='{1}' and recordid='{2}' and touserid<>'{0}' and signuserid is null;
        end if;
  end;", signUserID, tableID, recordID);

                DbCmdHelper cmd = new DbCmdHelper(dbProvider, sql);
                cmd.DbCmdInfo.CommandType = CommandType.Text;
                DbManager.ExecNonQuery(cmd);
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        public bool AutoSignData(Guid signUserID, int tableID, string tableName, int statusID)
        {
            try
            {
                string sql = string.Format(@"update workflow_operatelog set signuserid='{0}' , signtime=sysdate , islast=0 where recordid in (select dataid from {1} where statusID={2}) and tableid={3}", signUserID, tableName, statusID, tableID);
                DbCmdHelper cmd = new DbCmdHelper(dbProvider, sql);
                cmd.DbCmdInfo.CommandType = CommandType.Text;
                DbManager.ExecNonQuery(cmd);
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 获取某记录的流程日志
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        /// <param name="rtnDataTable"></param>
        /// <returns></returns>
        public string GetFlowLog(int tableID, Guid recordID, out DataTable workflowLogList)
        {
            workflowLogList = null;
            string strSql = string.Empty;
            string rtnStr = string.Empty;
            try
            {
                
                strSql = string.Format(@"select * from workflow_operatelog where tableID={0} and recordID='{1}' order by OperateTime", tableID.ToString(), recordID.ToString());
                DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql);
                cmd.DbCmdInfo.CommandType = CommandType.Text;
                workflowLogList = DbManager.ExecuteDataTable(cmd);
                
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        /// <summary>
        /// 获取流程所在的操作日志
        /// </summary>
        /// <param name="tableID"></param>
        /// <param name="recordID"></param>
        /// <param name="rtnDataTable"></param>
        /// <returns></returns>
        public string GetAllFlowLog(int tableID, string recordID, out DataTable workflowLogList)
        {
            workflowLogList = null;
            string strSql = string.Empty;
            string rtnStr = string.Empty;
            try
            {

                strSql = string.Format(@"select * from workflow_operatelog where tableID='{0}' and recordID='{1}'", tableID.ToString(), recordID.ToString());
                DbCmdHelper cmd = new DbCmdHelper(dbProvider, strSql);
                cmd.DbCmdInfo.CommandType = CommandType.Text;
                workflowLogList = DbManager.ExecuteDataTable(cmd);
                
            }
            catch (Exception ex)
            {
                rtnStr = ex.Message;
            }
            return rtnStr;
        }

        /// <summary>
        /// 取代办
        /// </summary>
        ///<param name="tableName"></param>
        /// <param name="userID"></param>
        /// <param name="beginNum">分页开始数</param>
        /// <param name="count">页大小</param>
        /// <param name="sum">总数</param>
        /// <returns></returns>
        public DataTable GetAgent(ArchiveTableEnum tableEnum, string tableName, string userID, int beginNum, int count, string filter, ref int sum, FilterSortEnum sort = FilterSortEnum.OrderByOperateTimeACS)
        {
            DataTable rtnDataTable = null;

            string sqlFilter = string.Empty;
            if (!string.IsNullOrEmpty(filter))
            {
                sqlFilter += " and " + filter;
            }
            string doneAgent = string.Format(@"select * from (select t.*, rownum rn from 
                                                                          (
                                                                          select s.* from {0} s
                                                                          inner join workflow_operatelog l
                                                                          on l.tableid={4} and l.touserid='{1}'
                                                                          and s.DataID=l.recordid
                                                                          and l.islast=1 {5}
                                                                          {6}
                                                                          ) t) e
                                                                        where e.rn between {2} and {3}", tableName, userID, beginNum + 1, beginNum + count, (int)tableEnum, sqlFilter, this.GetFilterSort(tableEnum, sort));
            string countSql = string.Format(@"select count(s.DataID) from {0} s
                                                                          inner join workflow_operatelog l
                                                                          on l.tableid={1} and l.touserid='{2}'
                                                                          and s.DataID=l.recordid
                                                                          and l.islast=1 {3}
                                                                          {4}", tableName, (int)tableEnum, userID, sqlFilter, this.GetFilterSort(tableEnum, sort));

            DbCmdHelper cmd = new DbCmdHelper(dbProvider, doneAgent);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            rtnDataTable = DbManager.ExecuteDataTable(cmd);

            cmd = new DbCmdHelper(dbProvider, countSql);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            sum = ParseHelper.Parse<int>(DbManager.ExecScalar(cmd));
            
            return rtnDataTable;
        }

        /// <summary>
        /// 取已办
        /// </summary>
        ///<param name="tableName"></param>
        /// <param name="userID"></param>
        /// <param name="beginNum">分页开始数</param>
        /// <param name="count">页大小</param>
        /// <param name="sum">总数</param>
        /// <returns></returns>
        public DataTable GetDone(ArchiveTableEnum tableEnum, string tableName, string userID, int beginNum, int count, string filter, ref int sum, FilterSortEnum sort = FilterSortEnum.OrderByOperateTimeDESC)
        {
            DataTable rtnDataTable = null;
            
            if (!string.IsNullOrEmpty(filter))
            {
                filter = " and " + filter;
            }

            string doneSql = string.Format(@"select * from (select tem.*,rownum as n from (select s.* from {0} s inner join workflow_operatelog l on s.DataID=l.recordid and l.fromuserid='{3}' and l.TableID='{4}' and l.touserid is not null inner join (select t.DataID,max(t.operatelogid) as realID from (select * from {0} s inner join workflow_operatelog l on s.DataID=l.recordid and l.TableID='{4}' and l.fromuserid='{3}' and l.touserid is not null and l.OperateID>0 {5}) t group by t.DataID) te on te.realID=l.operatelogid  {6}) tem) t where t.n between {1} and {2}", tableName, beginNum + 1, beginNum + count, userID.ToString(), (int)tableEnum, filter, this.GetFilterSort(tableEnum, sort));
            string sumSql = string.Format(@"select count(s.DataID) as n from {0} s inner join workflow_operatelog l on s.DataID=l.recordid and l.fromuserid='{1}' and l.TableID='{2}' and l.touserid is not null inner join (select t.DataID,max(t.operatelogid) as realID from (select * from {0} s inner join workflow_operatelog l on s.DataID=l.recordid and l.TableID='{2}' and l.fromuserid='{1}' and l.touserid is not null and l.OperateID>0 {3}) t group by t.DataID) te on te.realID=l.operatelogid", tableName, userID, (int)tableEnum, filter);
            
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, doneSql);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            rtnDataTable = DbManager.ExecuteDataTable(cmd);

            cmd = new DbCmdHelper(dbProvider, sumSql);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            sum = ParseHelper.Parse<int>(DbManager.ExecScalar(cmd));
            
            return rtnDataTable;
        }

        private string GetFilterSort(ArchiveTableEnum tableEnum, FilterSortEnum sort)
        {
            string rtnStringSort = string.Empty;
            switch (sort)
            {
                case FilterSortEnum.OrderByCreateTimeASC:
                case FilterSortEnum.OrderByCreateTimeDESC:                  
                case FilterSortEnum.OrderByOperateTimeACS:
                    rtnStringSort = "order by l.operatetime asc";
                    break;
                case FilterSortEnum.OrderByOperateTimeDESC:
                    rtnStringSort = "order by l.operatetime desc";
                    break;
                default:
                    break;
            }

            return rtnStringSort;
        }

        /// <summary>
        /// 获取已经入库的数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="userID"></param>
        /// <param name="beginNum"></param>
        /// <param name="count"></param>
        /// <param name="sum"></param>
        /// <param name="finishStatusID"></param>
        /// <returns></returns>
        public DataTable GetTotal(ArchiveTableEnum tableEnum, string tableName, string userID, int beginNum, int count, out int sum, int finishStatusID, string filter)
        {
            DataTable rtnDataTable = null;
            sum = 0;

            string sqlTotal = string.Empty;
            string sqlCount = string.Empty;
            if (!string.IsNullOrEmpty(userID))
            {
                if (!string.IsNullOrEmpty(filter))
                {
                    sqlTotal = string.Format("select * from (select A.*, rownum NumberIndex from (select * from {0} where statusid={1} and {2} and createuserid='{3}') A where rownum <= {4}) b where NumberIndex > {5}", tableName, finishStatusID, filter, userID, beginNum + count, beginNum);
                    sqlCount = string.Format("select count(*) from {0} where statusID={1} and {2} and createuserid='{3}'", tableName, finishStatusID, filter, userID);
                }
                else
                {
                    sqlTotal = string.Format("select * from (select A.*, rownum NumberIndex from (select * from {0} where statusid={1} and createuserid='{2}') A where rownum <= {3}) b where NumberIndex > {4}", tableName, finishStatusID, userID, beginNum + count, beginNum);
                    sqlCount = string.Format("select count(*) from {0} where statusID={1}  and createuserid='{2}'", tableName, finishStatusID, userID);
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(filter))
                {
                    sqlTotal = string.Format("select * from (select A.*, rownum NumberIndex from (select * from {0} where statusid={1} and {2} ) A where rownum <= {3}) b where NumberIndex > {4}", tableName, finishStatusID, filter, beginNum + count, beginNum);
                    sqlCount = string.Format("select count(*) from {0} where statusID={1} and {2}", tableName, finishStatusID, filter);
                }
                else
                {
                    sqlTotal = string.Format("select * from (select A.*, rownum NumberIndex from (select * from {0} where statusid={1} ) A where rownum <= {2}) b where NumberIndex > {3}", tableName, finishStatusID, beginNum + count, beginNum);
                    sqlCount = string.Format("select count(*) from {0} where statusID={1}", tableName, finishStatusID);
                }
            }

            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sqlTotal);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            rtnDataTable = DbManager.ExecuteDataTable(cmd);

            cmd = new DbCmdHelper(dbProvider, sqlTotal);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            sum = ParseHelper.Parse<int>(DbManager.ExecScalar(cmd));
            
            return rtnDataTable;
        }

        public DataTable GetTotalAll(ArchiveTableEnum tableEnum, string tableName, string userID, int finishStatusID, string filter)
        {
            DataTable rtnDataTable = null;
            
            string sqlTotal = string.Empty;
            if (string.IsNullOrEmpty(userID))
            {
                if (!string.IsNullOrEmpty(filter))
                {
                    sqlTotal = string.Format("select * from {0} where statusID ={1} and {2}", tableName, finishStatusID, filter);
                }
                else
                {
                    sqlTotal = string.Format("select * from {0} where statusID ={1}", tableName, finishStatusID);
                }
            }
            else
            {
                //先不实现，设计到是否查询某用户的数据
            }

            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sqlTotal);
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            rtnDataTable = DbManager.ExecuteDataTable(cmd);
            
            return rtnDataTable;
        }

        /// <summary>
        /// 获取角色下的所有用户
        /// </summary>
        /// <param name="roleID"></param>
        /// <returns></returns>
        public DataTable GetUsersByRoleID(string roleID)
        {
            string sql = @"select ur.roles_id as role_id, u.user_id, u.user_name from base_userinfo u 
                            join base_userrole ur on u.user_id = ur.user_id
                            where ur.roles_id = :p_role_id";
            DbCmdHelper cmd = new DbCmdHelper(dbProvider, sql, delegate
            {
                return DbCmdHelper.CreateParameters(dbProvider, "role_id").ToArray();
            });
            cmd.DbCmdInfo.CommandType = CommandType.Text;
            cmd.SetParamValues(roleID);
            return DbManager.ExecuteDataTable(cmd);
        }

        /// <summary>
        /// 获取角色下、单位下所有用户
        /// </summary>
        /// <param name="roleID"></param>
        /// <param name="orgID"></param>
        /// <returns></returns>
        public DataTable GetUsersByRoleIDAndOrgID(string roleID, string orgID)
        {
            return null;
        }
    }
}